﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.IO;
using System.Runtime.Serialization.Formatters.Binary;
using System.Data;
using Nollie.Utilities;
using Nollie.Storage.Database.Mapping;

namespace Nollie.Storage.Database
{
	public class SqlServerStorage : IDatabaseStorage
	{
		private CacheConfiguration _configuration;

		public SqlServerStorage(CacheConfiguration configuration)
		{
			_configuration = configuration;
		}
		private SqlCommand GetCommand()
		{
			var cmd = new SqlCommand();
			cmd.CommandType = System.Data.CommandType.Text;
			cmd.Connection = new SqlConnection(_configuration.ConnectionString);

			return cmd;
		}
		public void Add(string partitionName, CacheItem cacheItem)
		{
			int id = 0;
			
			using (var cmd = GetCommand())
			{
				cmd.CommandText = @"delete from $Cache where [Key] = @Key and PartitionName = @PartitionName;

									insert into $Cache(PartitionName, [Key], Value, ExpiryDate, AddedDate)
									values(@PartitionName, @Key, @Value, @ExpiryDate, @AddedDate);
									
									set @Id = scope_identity();";

				cmd.SetObjectName("Cache", _configuration.DatabaseTableNameCache);

				cmd.AddIntOutput("Id");
				cmd.AddVarChar("PartitionName", partitionName);
				cmd.AddVarChar("Key", cacheItem.Key);
				cmd.AddVarBinary("Value", SerializationHelper.SerializeObject(cacheItem.Value));
				cmd.AddDateTime("ExpiryDate", cacheItem.ExpiryDate);
				cmd.AddDateTime("AddedDate", DateTime.Now);

				try
				{
					cmd.Connection.Open();
					cmd.Transaction = cmd.Connection.BeginTransaction();

					cmd.ExecuteNonQuery();
					id = (int) cmd.Parameters["@Id"].Value;

					foreach (var tag in cacheItem.Tags)
					{
						cmd.CommandText = @"insert into $CacheTag(CacheId, Tag)
										values(@CacheId, @Tag);";

						cmd.SetObjectName("CacheTag", _configuration.DatabaseTableNameCacheTag);

						cmd.Parameters.Clear();
						cmd.AddInt("CacheId", id);
						cmd.AddVarChar("Tag", tag);

						cmd.ExecuteNonQuery();
					}

					cmd.Transaction.Commit();
				}
				catch (Exception ex)
				{
					throw ex;
				}
				finally
				{
					if (cmd.Transaction != null)
						cmd.Transaction.Rollback();

					cmd.Connection.Close();
				}
			}
		}
		public CacheItem Get(string partitionName, string key)
		{
			CacheItem cacheItem = null;

			using (var cmd = GetCommand())
			{
				cmd.CommandText = @"select *
									from $Cache c
									left join $CacheTag d on c.CacheId = d.CacheId
									where c.[Key] = @Key and c.PartitionName = @PartitionName;";

				cmd.SetObjectName("Cache", _configuration.DatabaseTableNameCache);
				cmd.SetObjectName("CacheTag", _configuration.DatabaseTableNameCacheTag);

				cmd.AddVarChar("PartitionName", partitionName);
				cmd.AddVarChar("Key", key);
				cmd.Connection.Open();

				using (var reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
				{
					while (reader.Read())
					{
						if (cacheItem == null)
							cacheItem = CacheItemMapper.MapToEntity(reader);

						if (!reader.IsDBNull("Tag"))
							cacheItem.Tags.Add(reader.GetString("Tag"));
					}
				}
			}

			return cacheItem;
		}
		public void RemoveByCacheKey(string partitionName, string key)
		{
			using (var cmd = GetCommand())
			{
				cmd.CommandText = @"delete from $Cache where [Key] = @Key and PartitionName = @PartitionName;";

				cmd.SetObjectName("Cache", _configuration.DatabaseTableNameCache);

				cmd.AddVarChar("PartitionName", partitionName);
				cmd.AddVarChar("Key", key);

				cmd.ExecuteNonQueryOpenCloseConnection();
			}
		}
		public void RemoveByTag(string partitionName, string key)
		{
			using (var cmd = GetCommand())
			{
				cmd.CommandText = @"delete from $Cache
									from $CacheTag d 
									where $Cache.CacheId = d.CacheId and $Cache.PartitionName = @PartitionName and d.Tag = @Key";

				cmd.SetObjectName("Cache", _configuration.DatabaseTableNameCache);
				cmd.SetObjectName("CacheTag", _configuration.DatabaseTableNameCacheTag);

				cmd.AddVarChar("PartitionName", partitionName);
				cmd.AddVarChar("Key", key);

				cmd.ExecuteNonQueryOpenCloseConnection();
			}
		}
		public void RemoveByTag(string key)
		{
			using (var cmd = GetCommand())
			{
				cmd.CommandText = @"delete from $Cache
									from $CacheTag d 
									where $Cache.CacheId = d.CacheId and d.Tag = @Key";

				cmd.SetObjectName("Cache", _configuration.DatabaseTableNameCache);
				cmd.SetObjectName("CacheTag", _configuration.DatabaseTableNameCacheTag);

				cmd.AddVarChar("Key", key);

				cmd.ExecuteNonQueryOpenCloseConnection();
			}
		}
		public void Clear(string partitionName)
		{
			using (var cmd = GetCommand())
			{
				cmd.CommandText = @"delete from $Cache where PartitionName = @PartitionName;";

				cmd.SetObjectName("Cache", _configuration.DatabaseTableNameCache);
				cmd.AddVarChar("PartitionName", partitionName);

				cmd.ExecuteNonQueryOpenCloseConnection();
			}
		}
		public void ClearAllPartitions()
		{
			using (var cmd = GetCommand())
			{
				cmd.CommandText = @"delete from $Cache";

				cmd.SetObjectName("Cache", _configuration.DatabaseTableNameCache);

				cmd.ExecuteNonQueryOpenCloseConnection();
			}
		}
		public int Count(string partitionName)
		{
			using (var cmd = GetCommand())
			{
				cmd.CommandText = @"select count(*)
									from $Cache c 
									where c.PartitionName = @PartitionName";
				
				cmd.SetObjectName("Cache", _configuration.DatabaseTableNameCache);

				cmd.AddVarChar("PartitionName", partitionName);

				return cmd.GetScalar<int>();
			}
		}
	}
}